#include "widget.h"
#include "ui_widget.h"
#include <QDebug>
#include <QSqlQuery>
#include <QSqlError>

Widget::Widget(QWidget *parent)
    : QWidget(parent)
    , ui(new Ui::Widget)
{
    ui->setupUi(this);
}

Widget::~Widget()
{
    delete ui;
}


void Widget::on_pushButton_6_clicked()
{
    sqlDB = QSqlDatabase::addDatabase("QSQLITE");

    sqlDB.setDatabaseName("studentDB.db");

    if(!sqlDB.open())
    {
        qDebug() << "打开数据库失败:" << sqlDB.lastError().text();
        return;
    }
    qDebug() << "打开数据库成功";
}

//关闭数据库
void Widget::on_pushButton_7_clicked()
{
    qDebug() << "关闭数据库成功";
    sqlDB.close();
}

//创建表
void Widget::on_pushButton_5_clicked()
{
    //建表语句
    QString sql("CREATE TABLE IF NOT EXISTS student(\
                    id INTEGER PRIMARY KEY AUTOINCREMENT,\
                    name TEXT NOT NULL,\
                    age INTEGER,\
                    cf INTEGER\
                );");
    QSqlQuery query;
    if(!query.exec(sql))
    {
        qDebug() << "创建表失败:" << query.lastError().text();
        return;
    }
    qDebug() << "建表成功";
}

//插入数据
void Widget::on_pushButton_clicked()
{
    QSqlQuery query;
    query.prepare("INSERT INTO student(name, age, cf) VALUES(:name, :age, :cf)");
    query.bindValue(":name", "张三");
    query.bindValue(":age", "18");
    query.bindValue(":cf", "1");

    if(!query.exec())
    {
        qDebug() << "插入数据失败:" << query.lastError().text();
        return;
    }
    qDebug() << "插入数据成功";

    query.prepare("INSERT INTO student(name, age, cf) VALUES(?, ?, ?)");
    query.bindValue(0, "李四");
    query.bindValue(1, "20");
    query.bindValue(2, "0");

    if(!query.exec())
    {
        qDebug() << "插入数据失败:" << query.lastError().text();
        return;
    }
    qDebug() << "插入数据成功";

    query.prepare("INSERT INTO student(name, age, cf) VALUES(?, ?, ?)");
    query.bindValue(0, "王五");
    query.bindValue(1, "22");
    query.bindValue(2, "15");

    if(!query.exec())
    {
        qDebug() << "插入数据失败:" << query.lastError().text();
        return;
    }
    qDebug() << "插入数据成功";
}

//查询
void Widget::on_pushButton_4_clicked()
{
    QString sql("SELECT * FROM student");
    QSqlQuery query;
    if(!query.exec(sql))
    {
        qDebug() << "查询数据失败:" << query.lastError().text();
        return;
    }

    while(query.next())
    {
        qDebug() << query.value(0).toInt() << " "
                 << query.value(1).toString() << " "
                 << query.value(2).toInt() << " "
                 << query.value(3).toInt();
    }
}

//更新
void Widget::on_pushButton_3_clicked()
{
    QSqlQuery query;
    query.prepare("UPDATE student SET cf = ? WHERE name = ?");
    query.bindValue(0, 1);
    query.bindValue(1, "李四");

    if(!query.exec())
    {
        qDebug() << "更新数据失败:" << query.lastError().text();
        return;
    }

    QString sql("SELECT * FROM student WHERE name = '李四'");
    if(!query.exec(sql))
    {
        qDebug() << "查询数据失败:" << query.lastError().text();
        return;
    }

    while(query.next())
    {
        qDebug() << query.value(0).toInt() << " "
                 << query.value(1).toString() << " "
                 << query.value(2).toInt() << " "
                 << query.value(3).toInt();
    }
}

//删除数据
void Widget::on_pushButton_2_clicked()
{
    QSqlQuery query;
    query.prepare("DELETE FROM student WHERE name = ?");
    query.addBindValue("王五");
    if(!query.exec())
    {
        qDebug() << "更新数据失败:" << query.lastError().text();
        return;
    }

    QString sql("SELECT * FROM student WHERE name = '王五'");
    if(!query.exec(sql))
    {
        qDebug() << "删除数据失败:" << query.lastError().text();
        return;
    }

    while(query.next())
    {
        qDebug() << query.value(0).toInt() << " "
                 << query.value(1).toString() << " "
                 << query.value(2).toInt() << " "
                 << query.value(3).toInt();
    }
}
